from psycopg import sql
from psycopg_pool import ConnectionPool
from psycopg.rows import dict_row
from contextlib import contextmanager
from typing import Any, Dict, List, Optional, Union
import logging
import json
from datetime import datetime

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class DatabaseManager:
    """基于 psycopg 的数据库操作封装类"""
    
    def __init__(self, dsn: str, min_conn: int = 1, max_conn: int = 10):
        """
        初始化数据库连接池
        
        Args:
            dsn: 数据库连接字符串，如 "postgresql://user:password@host:port/database"
            min_conn: 最小连接数
            max_conn: 最大连接数
        """
        self.dsn = dsn
        self.min_conn = min_conn
        self.max_conn = max_conn
        self._pool: Optional[ConnectionPool] = None
        self._init_pool()
    
    def _init_pool(self):
        """初始化连接池"""
        try:
            self._pool = ConnectionPool(
                self.dsn,
                min_size=self.min_conn,
                max_size=self.max_conn,
                timeout=30,
                kwargs={"row_factory": dict_row}  # 返回字典格式的结果
            )
            logger.info("数据库连接池初始化成功")
        except Exception as e:
            logger.error(f"数据库连接池初始化失败: {e}")
            raise
    
    @contextmanager
    def get_connection(self):
        """获取数据库连接的上下文管理器"""
        conn = None
        if self._pool is None:
            raise RuntimeError("数据库连接池未初始化")
        try:
            conn = self._pool.getconn()
            yield conn
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error(f"数据库操作失败: {e}")
            raise
        finally:
            if conn:
                self._pool.putconn(conn)
    
    @contextmanager
    def get_cursor(self):
        """获取游标的上下文管理器"""
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                try:
                    yield cursor
                    conn.commit()
                except Exception:
                    conn.rollback()
                    raise
    
    def execute(self, query: str, params: Optional[tuple] = None) -> int:
        """
        执行SQL语句（增删改）
        
        Args:
            query: SQL语句
            params: 参数元组
            
        Returns:
            影响的行数
        """
        with self.get_cursor() as cursor:
            cursor.execute(query, params)
            return cursor.rowcount
    
    def fetch_one(self, query: str, params: tuple = None) -> Optional[Dict]:
        """
        查询单条记录
        
        Args:
            query: SQL语句
            params: 参数元组
            
        Returns:
            单条记录字典或None
        """
        with self.get_cursor() as cursor:
            cursor.execute(query, params)
            return cursor.fetchone()
    
    def get_user_by_username(self, table_name: str, username: str):
        with self.get_cursor() as cursor:
            get_user_query=sql.SQL("""
                SELECT * FROM {table} WHERE username = %s
            """).format(table=sql.Identifier(table_name))
            cursor.execute(get_user_query, (username,))
            return cursor.fetchall()
    
    def fetch_all(self, query: str, params: tuple = None) -> List[Dict]:
        """
        查询多条记录
        
        Args:
            query: SQL语句
            params: 参数元组
            
        Returns:
            记录字典列表
        """
        with self.get_cursor() as cursor:
            cursor.execute(query, params)
            return cursor.fetchall()
    
    def insert(self, table: str, data: Dict) -> Any:
        """
        插入单条记录
        
        Args:
            table: 表名
            data: 数据字典
            
        Returns:
            插入的主键值
        """
        columns = ", ".join(data.keys())
        placeholders = ", ".join([f"%({key})s" for key in data.keys()])
        query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders}) RETURNING id"
        
        with self.get_cursor() as cursor:
            cursor.execute(query, data)
            result = cursor.fetchone()
            return result['id'] if result else None
    
    def batch_insert(self, table: str, data_list: List[Dict]) -> List[Any]:
        """
        批量插入记录
        
        Args:
            table: 表名
            data_list: 数据字典列表
            
        Returns:
            插入的主键值列表
        """
        if not data_list:
            return []
        
        columns = ", ".join(data_list[0].keys())
        placeholders = ", ".join([f"%({key})s" for key in data_list[0].keys()])
        query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders}) RETURNING id"
        
        ids = []
        with self.get_cursor() as cursor:
            for data in data_list:
                cursor.execute(query, data)
                result = cursor.fetchone()
                if result:
                    ids.append(result['id'])
            return ids
    
    def update(self, table: str, data: Dict, where: str, where_params: tuple = None) -> int:
        """
        更新记录
        
        Args:
            table: 表名
            data: 更新数据字典
            where: WHERE条件
            where_params: WHERE条件参数
            
        Returns:
            影响的行数
        """
        set_clause = ", ".join([f"{key} = %s" for key in data.keys()])
        query = f"UPDATE {table} SET {set_clause} WHERE {where}"
        
        # 合并参数：更新参数 + WHERE参数
        params = tuple(data.values()) 
        if where_params:
            params += where_params
        
        with self.get_cursor() as cursor:
            cursor.execute(query, params)
            return cursor.rowcount
    
    def delete(self, table: str, where: str, params: tuple = None) -> int:
        """
        删除记录
        
        Args:
            table: 表名
            where: WHERE条件
            params: 参数元组
            
        Returns:
            影响的行数
        """
        query = f"DELETE FROM {table} WHERE {where}"
        return self.execute(query, params)
    
    def exists(self, table: str, where: str, params: tuple = None) -> bool:
        """
        检查记录是否存在
        
        Args:
            table: 表名
            where: WHERE条件
            params: 参数元组
            
        Returns:
            是否存在
        """
        query = f"SELECT 1 FROM {table} WHERE {where} LIMIT 1"
        result = self.fetch_one(query, params)
        return result is not None
    
    def count(self, table: str, where: str = "1=1", params: tuple = None) -> int:
        """
        统计记录数量
        
        Args:
            table: 表名
            where: WHERE条件
            params: 参数元组
            
        Returns:
            记录数量
        """
        query = f"SELECT COUNT(*) as count FROM {table} WHERE {where}"
        result = self.fetch_one(query, params)
        return result['count'] if result else 0
    
    def close(self):
        """关闭连接池"""
        if self._pool:
            self._pool.close()
            logger.info("数据库连接池已关闭")

class UserConfigRepository:
    """用户配置表专用操作类"""
    
    def __init__(self, db: DatabaseManager):
        self.db = db
    
    def create_tables(self):
        """创建用户和配置表"""
        # 创建用户表
        self.db.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id BIGSERIAL PRIMARY KEY,
                username VARCHAR(50) UNIQUE NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                status VARCHAR(20) DEFAULT 'active' 
                    CHECK (status IN ('active', 'inactive', 'suspended')),
                created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # 创建配置表
        self.db.execute("""
            CREATE TABLE IF NOT EXISTS user_configs (
                id BIGSERIAL PRIMARY KEY,
                user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                config_key VARCHAR(100) NOT NULL,
                config_value TEXT,
                config_type VARCHAR(20) DEFAULT 'string' 
                    CHECK (config_type IN ('string', 'number', 'boolean', 'json', 'array')),
                description TEXT,
                is_active BOOLEAN DEFAULT TRUE,
                version INTEGER DEFAULT 1,
                created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
                created_by BIGINT,
                updated_by BIGINT,
                UNIQUE(user_id, config_key, is_active)
            )
        """)
        
        # 创建索引
        self.db.execute("CREATE INDEX IF NOT EXISTS idx_user_configs_user_id ON user_configs(user_id)")
        self.db.execute("CREATE INDEX IF NOT EXISTS idx_user_configs_key ON user_configs(config_key)")
        self.db.execute("CREATE INDEX IF NOT EXISTS idx_user_configs_active ON user_configs(is_active)")
        
        logger.info("数据库表创建完成")
    
    def create_user(self, username: str, email: str, **kwargs) -> int:
        """创建用户"""
        data = {
            "username": username,
            "email": email,
            **kwargs
        }
        return self.db.insert("users", data)
    
    def get_user(self, user_id: int = None, username: str = None, email: str = None) -> Optional[Dict]:
        """获取用户信息"""
        if user_id:
            return self.db.fetch_one("SELECT * FROM users WHERE id = %s", (user_id,))
        elif username:
            return self.db.fetch_one("SELECT * FROM users WHERE username = %s", (username,))
        elif email:
            return self.db.fetch_one("SELECT * FROM users WHERE email = %s", (email,))
        return None
    
    def set_config(self, user_id: int, config_key: str, config_value: Any, 
                   config_type: str = "string", description: str = None) -> bool:
        """设置用户配置"""
        try:
            # 停用旧的活跃配置
            self.db.update(
                "user_configs", 
                {"is_active": False}, 
                "user_id = %s AND config_key = %s AND is_active = TRUE",
                (user_id, config_key)
            )
            
            # 获取新版本号
            latest_version = self.db.fetch_one(
                "SELECT COALESCE(MAX(version), 0) as max_version FROM user_configs WHERE user_id = %s AND config_key = %s",
                (user_id, config_key)
            )
            new_version = (latest_version['max_version'] if latest_version else 0) + 1
            
            # 插入新配置
            config_data = {
                "user_id": user_id,
                "config_key": config_key,
                "config_value": str(config_value),
                "config_type": config_type,
                "description": description,
                "version": new_version,
                "is_active": True
            }
            
            self.db.insert("user_configs", config_data)
            return True
            
        except Exception as e:
            logger.error(f"设置用户配置失败: {e}")
            return False
    
    def get_config(self, user_id: int, config_key: str) -> Optional[Any]:
        """获取用户配置"""
        result = self.db.fetch_one(
            "SELECT config_value, config_type FROM user_configs WHERE user_id = %s AND config_key = %s AND is_active = TRUE",
            (user_id, config_key)
        )
        
        if result:
            return self._parse_config_value(result['config_value'], result['config_type'])
        return None
    
    def get_all_configs(self, user_id: int) -> Dict[str, Any]:
        """获取用户所有配置"""
        results = self.db.fetch_all(
            "SELECT config_key, config_value, config_type FROM user_configs WHERE user_id = %s AND is_active = TRUE",
            (user_id,)
        )
        
        configs = {}
        for row in results:
            configs[row['config_key']] = self._parse_config_value(row['config_value'], row['config_type'])
        
        return configs
    
    def delete_config(self, user_id: int, config_key: str) -> bool:
        """删除用户配置"""
        try:
            affected = self.db.update(
                "user_configs",
                {"is_active": False},
                "user_id = %s AND config_key = %s AND is_active = TRUE",
                (user_id, config_key)
            )
            return affected > 0
        except Exception as e:
            logger.error(f"删除用户配置失败: {e}")
            return False
    
    def _parse_config_value(self, value: str, config_type: str) -> Any:
        """解析配置值"""
        if value is None:
            return None
        
        try:
            if config_type == 'number':
                return float(value) if '.' in value else int(value)
            elif config_type == 'boolean':
                return value.lower() in ('true', '1', 'yes', 'on')
            elif config_type in ('json', 'array'):
                return json.loads(value)
            else:  # string
                return value
        except (json.JSONDecodeError, ValueError):
            logger.warning(f"配置值解析失败: {value}, 类型: {config_type}")
            return value
    
    def get_config_history(self, user_id: int, config_key: str, limit: int = 10) -> List[Dict]:
        """获取配置变更历史"""
        return self.db.fetch_all("""
            SELECT config_value, config_type, version, created_at, is_active
            FROM user_configs 
            WHERE user_id = %s AND config_key = %s 
            ORDER BY version DESC 
            LIMIT %s
        """, (user_id, config_key, limit))

if __name__ == '__main__':
    print('hello')
    database_manager1 = DatabaseManager(
        dsn='postgresql://myuser:mysecretpassword@172.21.103.200:5432/appdb'
    )
    result =database_manager1.get_user_by_username(table_name='users', username='ninglong')
    print(result)